package dao.impl;

import dao.CategoryDao;
import pojo.Category;
import pojo.User;
import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wq3stone
 */
public class CategoryDaoImpl implements CategoryDao {
    private static Connection conn = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;
    @Override
    public void insertFirstCategory(int uid,String name) {
        try {
            String sql = "insert into t_category (name,uid) values(?,?)";
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setInt(2,uid);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,null);
        }
    }

    @Override
    public void insertSecondCategory(int uid,int pcode,String name) {
        try {
            String sql = "insert into t_category (pcode,name,uid) values(?,?,?)";
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,pcode);
            ps.setString(2,name);
            ps.setInt(3,uid);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,null);
        }
    }

    @Override
    public void delete(int code) {
        try {
            String sql = "delete from t_category where code=?";
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,code);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,null);
        }
    }

    @Override
    public void update(Category category) {
        try {
            String sql = "update t_category set name=? where code=?";
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,category.getName());
            ps.setInt(2,category.getCode());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,null);
        }
    }

    @Override
    public List<Category> queryFirstCategory(int uid) {
        Category category = null;
        List<Category> list = new ArrayList<>();
        try {
            String sql = "select name,code from t_category where uid=? and pcode is null";
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,uid);
            rs = ps.executeQuery();
            while(rs.next()){
                String name = rs.getString("name");
                int code = rs.getInt("code");
                category = new Category(code, name, uid);
                list.add(category);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public List<Category> querySecondCategory(int pcode) {
        List<Category> list = new ArrayList<>();
        try {
            String sql = "select code,name,uid from t_category where pcode=?";
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,pcode);
            rs = ps.executeQuery();
            while(rs.next()){
                int code = rs.getInt("code");
                String name = rs.getString("name");
                int uid = rs.getInt("uid");
                Category category = new Category(code, pcode, name, uid);
                list.add(category);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public int queryFirstCode(int uid, String name) {
        int code = 0;
        try {
            String sql = "select code from t_category where uid=? and name=?";
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,uid);
            ps.setString(2,name);
            rs = ps.executeQuery();
            if(rs.next()){
                code = rs.getInt("code");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn,ps,rs);
        }
        return code;
    }

}
